﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Xml.Serialization;
using System.IO;

/*
 * String format for Coordinates
 * 6 boolen string (comma seperated) that announce our truck state.
 * 
 * If truck are enable, an equivalent pair of lat,lon will be sent, each seperated by ';'
 * The first one is prepend by ';' char in order to seperate the location from the truck state.
 * 
 * If a truck id is given with the QueryString, the truck location will be followed by an itinary.
 * Each itinary item will be comma seperated and each itinary will be seperated by ';'
 */

public partial class Coordinates : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // Get truck state in order to send them
        string Retour = GetTruckState() + ";";

        // TODO
        // Remove the next line, it is for debuging purpose only.
        string url = Page.Request.RawUrl;

        // Get trucks location
        if (!IsPostBack)
        {
            string[] truckState = Retour.Split(',');
            // Get truck enabled position
            for (int i = 0; i < truckState.Length; i++)
            {
                if (truckState[i] == "-1")
                    Retour += GetTruckLocation(i) + ";";
            }
        }

        // Switch on message type
        if (!IsPostBack)
        {
            string type = Page.Request.QueryString["Type"];

            switch (type)
            {
                case "ItineraireCamion":
                    // Get truck itinary
                    if (!String.IsNullOrEmpty(Page.Request.QueryString["Truck"]))
                    {
                        if (Page.Request.QueryString["Truck"] != "0")
                            Retour += GetTruckItinary(Page.Request.QueryString["Truck"]);
                    }
                    break;

                case "ChatMessage":
                    // Send chat message to database
                    if (!String.IsNullOrEmpty(Page.Request.QueryString["Pseudo"]) && !String.IsNullOrEmpty(Page.Request.QueryString["Message"]))
                    {
                        Retour += SendChatMessage(Page.Request.QueryString["Pseudo"], Page.Request.QueryString["Message"]);
                    }
                    break;
            }

        }

        // Get chat messages
        Retour += getNewChatMessages(Page.Request.QueryString["DateChat"]);

        if (!IsPostBack && !String.IsNullOrEmpty(Page.Request.QueryString["updatetrucklocation"]))
        {
            if (Page.Request.QueryString["updatetrucklocation"] == "true")
                Retour += UpdateTruckLocation();
        }

        // Write response to client
        Response.Write(Retour);

    }

    /// <summary>
    /// Get truck itinary
    /// </summary>
    /// <param name="truckNo"></param>
    /// <returns>(string)Truck count; (string)Truck location in lat lon, then all places that are in its itinaray, all seperated by ';'</returns>
    private string GetTruckItinary(string truckNo)
    {
        int truckID = 0;
        string Retour = "";
        if (Int32.TryParse(truckNo, out truckID))
        {
            string strAccessSelect = "SELECT Latitude, Longitude, Lieu FROM tblItineraire WHERE IDCamion = " + truckNo + " ORDER BY Latitude ASC";
            DataTable table = executeQuery(strAccessSelect);
            if (table != null)
            {
                Retour += table.Rows.Count.ToString() + ";";
                foreach (DataRow dr in table.Rows)
                    Retour += dr[0].ToString() + "," + dr[1].ToString() + "," + dr[2].ToString() + ";";
            }
            else
            {
                Trace.Warn("Error reading Database in GetTruckLocation");
            }
        }

        return Retour;
    }

    /// <summary>
    /// Get all truck state
    /// </summary>
    /// <returns>Comma seperated value representing truck state</returns>
    private string GetTruckState()
    {
        string Retour = "";
        string strAccessSelect = "SELECT Actif FROM tblCamion";
        DataTable table = executeQuery(strAccessSelect);

        if (table != null)
        {
            foreach (DataRow dr in table.Rows)
                Retour += dr[0].ToString() + ",";
            Retour = Retour.Remove(Retour.Length - 1);
        }
        else
        {
            Trace.Warn("Error reading Database in GetTruckState");
        }

        return Retour;
    }

    private string GetTruckLocation(int truck)
    {
        string Retour = "";
        string strAccessSelect = "SELECT Latitude, Longitude, IDCamion FROM tblPosition ORDER BY IDCamion ASC";
        DataTable table = executeQuery(strAccessSelect);

        if (table != null)
        {
            if (table.Rows.Count != 0)
                Retour = table.Rows[truck][0].ToString() + "," + table.Rows[truck][1].ToString() + "," + table.Rows[truck][2].ToString();
            else
                Retour = "0,0";
        }
        else
        {
            Trace.Warn("Error reading Database in GetTruckLocation");
        }

        return Retour;
    }

    private string UpdateTruckLocation()
    {
        // Truck update location 
        // Request string format : 
        /*
         * &updatetrucklocation=true  // enable truck info update
         * &latitude=123.456
         * &longitude=456.789
         * &truckid=08 
         */

        string Retour = ";";
        string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\gpsserver\\navigateur.mdb";
        string strAccessSelect = "UPDATE tblPosition SET Latitude='" + Page.Request.QueryString["latitude"] + "', Longitude='" + Page.Request.QueryString["Longitude"] + "' WHERE IDCamion=" + Page.Request.QueryString["truckid"];

        DataTable coordTable = new DataTable();
        OleDbConnection myAccessConn = new OleDbConnection(strAccessConn);
        OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn);

        try
        {
            myAccessConn.Open();
            myAccessCommand.ExecuteNonQuery();
            myAccessCommand.Dispose();
            myAccessConn.Close();
            myAccessConn.Dispose();
            Retour = "0";
        }
        catch (Exception ee)
        {
            Trace.Warn("Error reading Database in GetTruckLocation : ", ee.Message + " String : " + strAccessSelect);
            Retour = "1 " + ee.Message + " String = " + strAccessSelect;
        }
        //bebaye

        return Retour;
    }

    private string SendChatMessage(string pseudo, string message)
    {
        string Retour = "";
        string strAccessSelect = "INSERT INTO tblChatbox (DateMessage, Pseudo, Message) VALUES ('" + DateTime.Now.ToString() + "', '" + pseudo + "', '" + message + "');" ;
        executeNonQuery(strAccessSelect);

        return Retour;

    }

    private string getNewChatMessages(string chatDate)
    {
        string str = "";
        string addStr = "";
        string strAccessSelect = "SELECT TOP 10 * FROM tblChatbox WHERE DateMessage > #" + chatDate + "# ORDER BY DateMessage DESC;";
        DataTable table = executeQuery(strAccessSelect);

        if (table != null)
        {
            str += table.Rows.Count + ";";
            foreach (DataRow dr in table.Rows)
            {
                addStr = dr[1] + "," + dr[2] + "," + dr[3] + ";" + addStr;
            }
        }
        else
        {
            Trace.Warn("Error reading Database in getNewChatMessages");
            str = "0;";
        }

        return str + addStr;
    }

    private DataTable executeQuery(string strAccessQuery)
    {
        string strAccessCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\gpsserver\\navigateur.mdb";
        DataTable table = new DataTable();
        OleDbConnection dbCon = new OleDbConnection(strAccessCon);
        OleDbCommand dbCom = new OleDbCommand(strAccessQuery, dbCon);
        OleDbDataAdapter dbDat = new OleDbDataAdapter(dbCom);
        try
        {
            dbCon.Open();
            dbDat.Fill(table);
            dbCon.Close();
            return table;
        }
        catch (Exception e)
        {
            Trace.Warn("Error while executing query : " + e.Message);
            dbCon.Close();
            return null;
        }
    }

    private void executeNonQuery(string strAccessQuery)
    {
        string strAccessCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\gpsserver\\navigateur.mdb";
        OleDbConnection dbCon = new OleDbConnection(strAccessCon);
        OleDbCommand dbCom = new OleDbCommand(strAccessQuery, dbCon);
        try
        {
            dbCon.Open();
            dbCom.ExecuteNonQuery();
            dbCon.Close();
        }
        catch (Exception e)
        {
            Trace.Warn("Error while executing non-query : " + e.Message);
            dbCon.Close();
        }
    }

}
